﻿-----function---------------
create function [dbo].[f_loainhanvien]
(
	 @manv varchar(10)
)
returns int
as
begin
	declare @loainv int;
	select @loainv=lnv.maloainv from loainv lnv, nhanvien nv where nv.manv=@manv and nv.loainv=lnv.maloainv;
	return @loainv;
end

-----------------------------------------



create trigger trg_kiemtradangkyquasoluong on dangkylichdi
for insert
as
begin
	declare @vtsl int 
	set @vtsl=0
	declare @manv varchar(10)
	declare @mtuyen int
	declare @mxe int
	declare @ngdi datetime
	select @manv= manv from Inserted 
	select @mtuyen =matuyen from Inserted
	select @mxe=maxe from Inserted
	select @ngdi=ngaydi from Inserted
	declare @lnv int
	set @lnv=dbo.f_loainhanvien(@manv) --- nhan vien vua dang ky la phu lai hay tai xe
	declare @sldi int --- so luong tai xe hay phu lai tren mot lich di
	if(@lnv=2)-----// neu la tai xe
		begin
			--- lay so luong tai xe can cho chuyen 
			select @sldi= sltaixe from lichdi where matuyen=@mtuyen and maxe=@mxe and ngaydi=@ngdi
		end
	else 
		if(@lnv=3)---- la phu lai
			begin
				---- lay so luong phu lai
				select @sldi= slphulai from lichdi where matuyen=@mtuyen and maxe=@mxe and ngaydi=@ngdi	
			end
	
	--- dem so luong dang ky trong bang dangkylichdi
	declare cur_danhsachdangky cursor
	for select manv from dangkylichdi where matuyen=@mtuyen and maxe=@mxe and ngaydi=@ngdi

	open cur_danhsachdangky
	declare @mnv varchar(10)
	fetch next from cur_danhsachdangky into @mnv
	while(@@fetch_status=0)
		begin
			if(dbo.f_loainhanvien(@mnv)=@lnv)
				begin
					-- tang bien dem sl da dang ky
					set @vtsl=@vtsl+1
				end
		fetch next from cur_danhsachdangky into @mnv
		end
	close cur_danhsachdangky
	deallocate cur_danhsachdangky

	if(@vtsl>@sldi)
		begin
			raiserror('Vị trí này đã đủ người, vui lòng chọn lịch đi khác! cảm ơn.',16,1)
			rollback tran
		end
end
